package com.lwj.test.excel;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.BooleanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelReader {

    final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

    private FileInputStream inputStream;
    private HSSFWorkbook workbook;
    private int sheetIndex;
    /**
     * 开始读取数据行
     */
    private int startRow = 0;
    /**
     * 开始读取数据列
     */
    private int startColumn = 0;

    /**
     * 指定输入流 初始化WorkBook对象
     */
    private void initWorkbook() {
	if (workbook == null) {
	    try {
		workbook = new HSSFWorkbook(inputStream);
	    } catch (IOException e) {
		e.printStackTrace();
	    }
	}
    }

    /**
     * 获取所有行
     * 
     * @return
     */
    public List<HSSFRow> readHSSFRows() {
	HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
	// 实际存在的行数
	int rows = sheet.getPhysicalNumberOfRows();
	List<HSSFRow> hssfRows = new ArrayList<HSSFRow>();

	for (int count = startRow; count <= rows - 1; count++) {
	    HSSFRow row = sheet.getRow(count);
	    if (row != null) {
		hssfRows.add(row);
	    }
	}
	return hssfRows;
    }

    /**
     * 读取行数据
     * 
     * @param hssfRow
     * @return
     */
    public String[] readCellsValue(HSSFRow hssfRow) {

	int cells = hssfRow.getPhysicalNumberOfCells();// 列数
	System.out.println("\nROW " + hssfRow.getRowNum() + " has " + cells
		+ " cell(s).");
	String[] cellValues = new String[cells];
	for (int counter = startColumn; counter < cells; counter++) {
	    HSSFCell cell = hssfRow.getCell((short) counter);
	    String value = null;
	    switch (cell.getCellType()) {
	    case HSSFCell.CELL_TYPE_FORMULA:
		value = cell.getCellFormula();
		break;
	    case HSSFCell.CELL_TYPE_NUMERIC:
		value = String.valueOf(cell.getNumericCellValue());
		if (HSSFDateUtil.isCellDateFormatted(cell)) {
		    Date date = cell.getDateCellValue();
		    if (date != null) {
			value = format.format(date);
		    } else {
			value = "";
		    }
		} else {
		    value = new DecimalFormat("0").format(cell
			    .getNumericCellValue());
		}

		break;
	    case HSSFCell.CELL_TYPE_STRING:
		value = cell.getStringCellValue();
		break;
	    default:
		value = "9999";
	    }
	    cellValues[counter] = value;
	}// end for
	return cellValues;
    }

    /**
     * 
     * 根据传入的Map;将cell的数值依次对应赋值给对象属性
     * 
     * @param propertiesMap
     *            :格式為[-1:class,0:filedname1;1:filedname2.....]
     *            其中：0代表第0个cell;1代表第1个cell
     * @param cells
     * @return
     */
    @SuppressWarnings("unchecked")
    public Object genVOFromCells(Map<Integer, String> propertiesMap,
	    String cells[]) {
	// 存放对象属性描述信息
	Map<String, PropertyDescriptor> propMap = new HashMap<String, PropertyDescriptor>();
	Object obj = null;
	String className = propertiesMap.get(-1);
	Map properDesc = null;
	try {
	    Class cls = Class.forName(className);
	    obj = cls.newInstance();
	    // 获取对象的描述信息
	    BeanInfo bi = Introspector.getBeanInfo(cls);
	    PropertyDescriptor[] pds = bi.getPropertyDescriptors();
	    for (PropertyDescriptor p : pds) {
		propMap.put(p.getName(), p);
	    }
	    properDesc = BeanUtils.describe(obj);
	    Set<Integer> keys = propertiesMap.keySet();
	    Iterator<Integer> iter = keys.iterator();
	    while (iter.hasNext()) {
		Integer index = iter.next();
		if (index.intValue() == -1) {
		    continue;
		}
		String property = propertiesMap.get(index);
		if (properDesc.containsKey(property)) {
		    // 获取对象类型
		    Class<?> propertyType = propMap.get(property)
			    .getPropertyType();
		    System.out.println(property + ".class:" + propertyType);
		    String value = cells[index];
		    if (String.class.equals(propertyType)) {
			PropertyUtils.setProperty(obj, property, value);
		    } else if (Date.class.equals(propertyType)) {
			Date d;
			try {
			    d = format.parse(value);
			} catch (Exception e) {
			    d = new Date();
			}
			PropertyUtils.setProperty(obj, property, d);
		    } else if (int.class.equals(propertyType)
			    || Integer.class.equals(propertyType)) {
			int i = 0;
			try {
			    i = Integer.parseInt(value);
			} catch (NumberFormatException e) {
			    i = 0;
			}
			PropertyUtils.setProperty(obj, property, i);
		    } else if (double.class.equals(propertyType)
			    || Double.class.equals(propertyType)) {
			double d = 0;
			try {
			    d = Double.parseDouble(value);
			} catch (NumberFormatException e) {
			    d = 0.0;
			}
			PropertyUtils.setProperty(obj, property, d);
		    } else if (long.class.equals(propertyType)
			    || Long.class.equals(propertyType)) {
			long l = 0;
			try {
			    l = Long.parseLong(value);
			} catch (NumberFormatException e) {
			    l = 0l;
			}
			PropertyUtils.setProperty(obj, property, l);
		    } else if (float.class.equals(propertyType)
			    || Float.class.equals(propertyType)) {
			float f = 0;
			try {
			    f = Float.parseFloat(value);
			} catch (NumberFormatException e) {
			    f = 0l;
			}
			PropertyUtils.setProperty(obj, property, f);
		    } else {// boolean
			Boolean b = BooleanUtils.toBooleanObject(value);
			PropertyUtils.setProperty(obj, property, b);
		    }
		}
	    }
	} catch (Exception e) {
	    e.printStackTrace();
	}
	return obj;
    }

    /**
     * 设置输入流； 必须为Excel指定输入流
     * 
     * @param inputStream
     */
    public void setInputStream(FileInputStream inputStream) {
	this.inputStream = inputStream;
	initWorkbook();
    }

    /**
     * 设置读取sheet
     * 
     * @param sheetIndex
     */
    public void setSheetIndex(int sheetIndex) {
	this.sheetIndex = sheetIndex;
    }

    /**
     * 设置读取数据起始行 默认从0开始
     * 
     * @param startRow
     */
    public void setStartRow(int startRow) {
	this.startRow = startRow;
    }

    /**
     * 设置行数据，读取数据起始列 默认从0开始
     * 
     * @param startColumn
     */
    public void setStartColumn(int startColumn) {
	this.startColumn = startColumn;
    }

    public static void main(String args[]) {

	Map<Integer, String> propertiesMap = new HashMap<Integer, String>();
	propertiesMap.put(-1, "com.randy.VO");
	propertiesMap.put(0, "num");
	propertiesMap.put(1, "str");
	propertiesMap.put(2, "date");

	ExcelReader reader = new ExcelReader();
	FileInputStream excelFile = null;
	try {
	    excelFile = new FileInputStream("F:\\workbook.xls");
	} catch (FileNotFoundException e) {
	    e.printStackTrace();
	}
	reader.setInputStream(excelFile);
	List<HSSFRow> rows = reader.readHSSFRows();
	for (HSSFRow row : rows) {
	    String[] values = reader.readCellsValue(row);
	    for (String v : values) {
		System.out.print(v + "\t");
	    }
	    System.out.println();
	    Object obj = reader.genVOFromCells(propertiesMap, values);
	    System.out.println(obj);
	}

    }
}